const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'xiaomiyoupin',
  charset: 'utf8mb4'
};

// 读取商品分类数据文件
function readCategoryDataFile() {
  const filePath = path.join(__dirname, '..', '商品分类数据');
  const fileContent = fs.readFileSync(filePath, 'utf8');
  
  // 分割文件内容，每个JSON对象一行
  const jsonObjects = [];
  const lines = fileContent.split('\n');
  let currentJson = '';
  let braceCount = 0;
  
  for (const line of lines) {
    currentJson += line;
    
    // 计算大括号数量
    for (const char of line) {
      if (char === '{') braceCount++;
      if (char === '}') braceCount--;
    }
    
    // 当大括号平衡时，说明一个JSON对象结束
    if (braceCount === 0 && currentJson.trim()) {
      try {
        const jsonObj = JSON.parse(currentJson.trim());
        if (jsonObj.code === 0 && jsonObj.data) {
          jsonObjects.push(jsonObj.data);
        }
      } catch (error) {
        console.warn('解析JSON失败:', error.message);
      }
      currentJson = '';
    }
  }
  
  return jsonObjects;
}

// 提取分类数据
function extractCategories(dataObjects) {
  const categories = [];
  let sortOrder = 1;
  
  for (const dataObj of dataObjects) {
    // 主分类
    if (dataObj.name && dataObj.id) {
      const mainCategory = {
        name: dataObj.name,
        originalId: dataObj.id,
        parentId: 0,
        level: 1,
        sortOrder: dataObj.sort ? Math.floor(dataObj.sort / 1000000000) + sortOrder : sortOrder,
        type: dataObj.type || 0,
        description: `小米有品${dataObj.name}分类`,
        isShow: 1,
        image: dataObj.banner || null
      };
      categories.push(mainCategory);
      sortOrder++;
      
      // 子分类
      if (dataObj.children && Array.isArray(dataObj.children)) {
        let subSortOrder = 1;
        for (const child of dataObj.children) {
          if (child.name && child.id) {
            const subCategory = {
              name: child.name,
              originalId: child.id,
              parentOriginalId: dataObj.id,
              parentId: 0, // 稍后会更新
              level: 2,
              sortOrder: child.sort ? Math.floor(child.sort / 1000000000) + subSortOrder : subSortOrder,
              type: child.type || 0,
              description: `${dataObj.name} - ${child.name}`,
              isShow: 1,
              image: null
            };
            categories.push(subCategory);
            subSortOrder++;
            
            // 三级分类
            if (child.children && Array.isArray(child.children)) {
              let thirdSortOrder = 1;
              for (const grandChild of child.children) {
                if (grandChild.smallImgCard && grandChild.smallImgCard.name) {
                  const thirdCategory = {
                    name: grandChild.smallImgCard.name,
                    originalId: grandChild.id,
                    parentOriginalId: child.id,
                    parentId: 0, // 稍后会更新
                    level: 3,
                    sortOrder: grandChild.sort ? Math.floor(grandChild.sort / 1000000000) + thirdSortOrder : thirdSortOrder,
                    type: 0,
                    description: `${dataObj.name} - ${child.name} - ${grandChild.smallImgCard.name}`,
                    isShow: 1,
                    image: grandChild.smallImgCard.img || null
                  };
                  categories.push(thirdCategory);
                  thirdSortOrder++;
                }
              }
            }
          }
        }
      }
    }
  }
  
  return categories;
}

// 建立父子关系
function buildParentChildRelations(categories) {
  // 创建ID映射
  const idMap = new Map();
  
  // 先插入所有一级分类，获取数据库ID
  const processedCategories = [];
  
  for (const category of categories) {
    if (category.level === 1) {
      processedCategories.push({
        ...category,
        parentId: 0
      });
    }
  }
  
  // 处理二级分类
  for (const category of categories) {
    if (category.level === 2) {
      // 找到父分类
      const parent = categories.find(c => c.level === 1 && c.originalId === category.parentOriginalId);
      if (parent) {
        processedCategories.push({
          ...category,
          parentId: 0 // 稍后通过数据库查询更新
        });
      }
    }
  }
  
  // 处理三级分类
  for (const category of categories) {
    if (category.level === 3) {
      processedCategories.push({
        ...category,
        parentId: 0 // 稍后通过数据库查询更新
      });
    }
  }
  
  return processedCategories;
}

async function importDetailedCategories() {
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功');

    // 读取和解析数据
    console.log('读取商品分类数据文件...');
    const dataObjects = readCategoryDataFile();
    console.log(`解析到 ${dataObjects.length} 个主要分类数据对象`);

    // 提取分类数据
    console.log('提取分类数据...');
    const categories = extractCategories(dataObjects);
    console.log(`提取到 ${categories.length} 个分类`);

    // 统计各级分类数量
    const levelStats = categories.reduce((stats, cat) => {
      stats[cat.level] = (stats[cat.level] || 0) + 1;
      return stats;
    }, {});
    console.log('分类级别统计:', levelStats);

    // 清空现有分类数据（可选）
    console.log('清空现有分类数据...');
    await connection.execute('DELETE FROM categories WHERE 1=1');
    await connection.execute('ALTER TABLE categories AUTO_INCREMENT = 1');

    // 分批插入数据
    console.log('开始插入分类数据...');
    let insertedCount = 0;
    
    // 先插入一级分类
    const level1Categories = categories.filter(c => c.level === 1);
    const level1IdMap = new Map();
    
    for (const category of level1Categories) {
      const insertSql = `
        INSERT INTO categories 
        (name, parent_id, level, sort_order, icon, image, description, is_show, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const values = [
        category.name,
        0,
        category.level,
        category.sortOrder,
        null, // icon
        category.image,
        category.description,
        category.isShow,
        new Date(),
        new Date()
      ];
      
      const [result] = await connection.execute(insertSql, values);
      level1IdMap.set(category.originalId, result.insertId);
      insertedCount++;
    }
    
    console.log(`插入 ${level1Categories.length} 个一级分类`);

    // 插入二级分类
    const level2Categories = categories.filter(c => c.level === 2);
    const level2IdMap = new Map();
    
    for (const category of level2Categories) {
      const parentDbId = level1IdMap.get(category.parentOriginalId) || 0;
      
      const insertSql = `
        INSERT INTO categories 
        (name, parent_id, level, sort_order, icon, image, description, is_show, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const values = [
        category.name,
        parentDbId,
        category.level,
        category.sortOrder,
        null, // icon
        category.image,
        category.description,
        category.isShow,
        new Date(),
        new Date()
      ];
      
      const [result] = await connection.execute(insertSql, values);
      level2IdMap.set(category.originalId, result.insertId);
      insertedCount++;
    }
    
    console.log(`插入 ${level2Categories.length} 个二级分类`);

    // 插入三级分类
    const level3Categories = categories.filter(c => c.level === 3);
    
    for (const category of level3Categories) {
      const parentDbId = level2IdMap.get(category.parentOriginalId) || 0;
      
      const insertSql = `
        INSERT INTO categories 
        (name, parent_id, level, sort_order, icon, image, description, is_show, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const values = [
        category.name,
        parentDbId,
        category.level,
        category.sortOrder,
        null, // icon
        category.image,
        category.description,
        category.isShow,
        new Date(),
        new Date()
      ];
      
      await connection.execute(insertSql, values);
      insertedCount++;
    }
    
    console.log(`插入 ${level3Categories.length} 个三级分类`);
    console.log(`总共成功插入 ${insertedCount} 条分类数据`);

    // 查询插入的数据统计
    const [stats] = await connection.execute(`
      SELECT 
        level,
        COUNT(*) as count,
        MIN(sort_order) as min_sort,
        MAX(sort_order) as max_sort
      FROM categories 
      GROUP BY level 
      ORDER BY level
    `);
    
    console.log('插入数据统计:');
    console.table(stats);

    // 显示部分插入的数据
    const [sampleData] = await connection.execute(`
      SELECT id, name, parent_id, level, sort_order, description 
      FROM categories 
      ORDER BY level, sort_order 
      LIMIT 20
    `);
    
    console.log('部分插入的分类数据:');
    console.table(sampleData);

  } catch (error) {
    console.error('导入分类数据失败:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 执行导入
importDetailedCategories();